<?php
include APPPATH . '../../excel/excel_reader2.php';

class Report extends Controller {

	private static $PERMISSION = "REPORT";

	function __construct()
	{
		parent::Controller();
		
		check_permission(self::$PERMISSION);
		
		$this->load->helper("file");
		$this->load->model("product_model");
		$this->load->model("report_model");
		$this->load->model("film_model");
		$this->load->model("program_model");
		$this->load->model("slit_model");
		$this->load->model("order_model");
		$this->load->model("coil_model");
	}
	
	function index()
	{
		$username = $this->session->userdata("USERNAME");
		user_log_message("INFO",  $username . " call report page.");
	
		$selected = "Report";
		$navigator = array(
			"หน้าแรก" => "/main",
			$selected => "/report",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
				
		$this->template->write_view('content', 'report_main_view', $data);
		$this->template->render();
	}
	
	function production_upload()
	{
	
		$username = $this->session->userdata("USERNAME");
		user_log_message("INFO",  $username . " call report production upload [ before delete all temporary file]");
		
		delete_files('upload/');
		
		$data = array("error" => "");
		
		$filenames = get_dir_file_info('./upload/');
		//$data["filenames"] = $filenames;
		
		$file_sort =array();
		foreach($filenames as $item)
		{
			$file_sort[$item["date"]] = $item;
		}
		krsort($file_sort);
		$data["filenames"] = $file_sort;
		
		$selected = "ทำการ upload ไฟล์";
		$navigator = array(
			"หน้าแรก" => "/main",
			"Report" => "/report",
			$selected => "/report/production_upload",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
		
		$this->template->write_view('content', 'report/production_upload_view', $data);
		$this->template->render();
	}
	
	function upload()
	{
		$username = $this->session->userdata("USERNAME");
		user_log_message("INFO",  $username . " call upload file");
	
		$config['upload_path'] = './upload/';
		$config['allowed_types'] = 'xls';
		$config['max_size']	= '0';
		$config['max_width']  = '0';
		$config['max_height']  = '0';
		$config['remove_spaces'] = TRUE;
		$config['file_name'] = mktime();
		
		$this->load->library('upload', $config);
		
		if ( ! $this->upload->do_upload())
		{
			$data = array('error' => $this->upload->display_errors());
			
			$filenames = get_dir_file_info('./upload/');
			//$data["filenames"] = $filenames;
			
			$file_sort =array();
			foreach($filenames as $item)
			{
				$file_sort[$item["date"]] = $item;
			}
			krsort($file_sort);
			$data["filenames"] = $file_sort;
			
			$selected = "ทำการ upload ไฟล์";
			$navigator = array(
				"หน้าแรก" => "/main",
				"Report" => "/report",
				$selected => "/report/production_upload",
			);
			
			$data["navigation"] = build_navigation($selected, $navigator);
			
			$username = $this->session->userdata("USERNAME");
			user_log_message("INFO",  $username . " upload error : " . $this->upload->display_errors());
			
			$this->template->write_view('content', 'report/production_upload_view', $data);
			$this->template->render();
		}	
		else
		{
			$excel_data = $this->upload->data();
			//print_r($excel_data);
			$excel = new Spreadsheet_Excel_Reader($excel_data["full_path"]);
			
			$prodcode_column = FALSE;
			$wsendqty_column = FALSE;
			$totalqty_column = FALSE;
			for($i = 'A'; $i <= 'Z' ; $i++)
			{
				
				// Find column name [prodcode]
				if ($excel->val(1, $i) == "prodcode")
				{
					$prodcode_column = $i;
				}
			
				// Find column name [wsendqty]
				if ($excel->val(1, $i) == "wsendqty")
				{
					$wsendqty_column = $i;
				}
				
				// Find column name [totalqty]
				if ($excel->val(1, $i) == "totalqty")
				{
					$totalqty_column = $i;
				}
				
			}
			
			
			$wsendqty_map = array();
			$totalqty_map = array();
			
			$username = $this->session->userdata("USERNAME");
			user_log_message("INFO",  $username . " get all data in excel");
		
			$row = $excel->rowcount(0);
			for($i = 2 ; $i < $row; $i++)
			{
				if ($prodcode_column !== FALSE)
				{
					$prodcode = $excel->val($i, $prodcode_column);
					if (!empty($prodcode))
					{
						if ($wsendqty_column !== FALSE)
						{
							$wsendqty = $excel->val($i , $wsendqty_column);
							$wsendqty_map[$prodcode] = $wsendqty;
						}
						
						if ($totalqty_column !== FALSE)
						{
							$totalqty = $excel->val($i, $totalqty_column);
							$totalqty_map[$prodcode] = $totalqty;
						}
					}
				}
			}
			
			$product_result = $this->product_model->get_all();
			
			$this->db->trans_begin();
			
			$this->report_model->clear();
			
			foreach($product_result as $item)
			{
				$product_display_id = $item->product_display_id;
				
				$delivery = 0;
				if (isset($wsendqty_map[$product_display_id]))
				{
					$delivery = $wsendqty_map[$product_display_id];
				}
				
				$backlog = 0;
				if (isset($totalqty_map[$product_display_id]))
				{
					$backlog = $totalqty_map[$product_display_id];
				}
				
				$this->report_model->insert("ADD", $product_display_id, $delivery, $backlog);
			}
			
			if ($this->db->trans_status() === FALSE)
			{
				$this->db->trans_rollback();
				
				$data['result'] = 'เกิดปัญหาขึ้นระหว่างการ upload หรือ ใส่ข้อมูลลง database<br/>Error Number : ' . $this->db->_error_number() . "<br/>" .$this->db->_error_message();
				$data['back_page'] = "/report";
				$this->template->write_view('content', 'order_result_view', $data);
				$this->template->render();
			}
			else
			{
				$this->db->trans_commit();
				
				$data['result'] = "ทำการ upload และ เก็บข้อมูลลง database เรียบร้อยแล้ว";
				$data['back_page'] = "/report";
				$this->template->write_view('content', 'order_result_view', $data);
				$this->template->render();
			}
		}
	}
	
	function production_read_file($file_path)
	{
		$path_to_file = $this->convert->HexToAscii($file_path);
		
		$data['upload_data']['full_path'] = $path_to_file;
		
		$this->production_report($data);
	}
	
	function production_report()
	{
		// Read Date
		$filenames = get_dir_file_info('./upload/');
		$max_date = FALSE;
		foreach($filenames as $item)
		{
			if ($max_date === FALSE)
			{
				$max_date = $item["date"];
			}
			else
			{
				if ($max_date < $item["date"])
				{
					$max_date = $item["date"];
				}
			}
		}
		$data["max_date"] = unix_to_human($max_date);
	
		$report_result = $this->report_model->get_vr_report();
		$data["report_result"] = $report_result;
		
		$film_percent = array();
		$program_ext = array();
		foreach($report_result as $item)
		{
			//$film_percent[$item["product_display_id"]] = $this->slit_model->get_sum_percent_film_by_product_id_and_thickness($item["product_dtl_id"], $item["thickness_rep"]);
			$program_ext[$item["product_dtl_id"]] =  $this->program_model->get_program_code_and_ext_by_product_dtl_id_with_normal_status($item["product_dtl_id"]);
		}
		//$data["film_percent"] = $film_percent;
		//print_r($program_ext);
		$data["program_ext"] = $program_ext;
	
		$username = $this->session->userdata("USERNAME");
		user_log_message("INFO",  $username . " print report to view");
		
		$selected = "Production report";
		$navigator = array(
			"หน้าแรก" => "/main",
			"Report" => "/report",
			$selected => "",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
				
		$this->template->write_view('content', 'report/production_report_view', $data);
		$this->template->render();
	}
	
	function finishgood_detail_report()
	{
		// Read Date
		$sort_column = $this->input->post("sort_column");
		$sort_by = $this->input->post("sort_by");
		
		if (empty($sort_column)) {
			$sort_column = "sortDate";
		}
		if (empty($sort_by)) {
			$sort_by = "desc";
		}		
	
		$report_result = $this->report_model->get_vr_finishgood_detail_report($sort_column, $sort_by);
		$data["report_result"] = $report_result;
		$data["sort_column"] = $sort_column;
		$data["sort_by"] = ("asc" == $sort_by) ? "desc" : "asc";
	
		$username = $this->session->userdata("USERNAME");
		user_log_message("INFO",  $username . " print report to view");
		
		$selected = "Finished goods detail report";
		$navigator = array(
			"หน้าแรก" => "/main",
			"Report" => "/report",
			$selected => "",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
				
		$this->template->write_view('content', 'report/finishgood_detail_report_view', $data);
		$this->template->render();
	}
	
	function finishgood_report()
	{
		// Read Date

	
		$report_result = $this->report_model->get_vr_finishgood_report();
		$data["report_result"] = $report_result;
	
		$username = $this->session->userdata("USERNAME");
		user_log_message("INFO",  $username . " print report to view");
		
		$selected = "Finished good summary report";
		$navigator = array(
			"หน้าแรก" => "/main",
			"Report" => "/report",
			$selected => "",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
				
		$this->template->write_view('content', 'report/finishgood_report_view', $data);
		$this->template->render();
	}
	
	function coil_received_report()
	{
		// Read Date
		$sort_column = $this->input->post("sort_column");
		$sort_by = $this->input->post("sort_by");
		
		if (empty($sort_column)) {
			$sort_column = "sortReceivedDate";
		}
		if (empty($sort_by)) {
			$sort_by = "desc";
		}
	
		$report_result = $this->report_model->get_vr_coil_received_report($sort_column, $sort_by);
		$data["report_result"] = $report_result;
		$data["sort_column"] = $sort_column;
		$data["sort_by"] = ("asc" == $sort_by) ? "desc" : "asc";
	
		$username = $this->session->userdata("USERNAME");
		user_log_message("INFO",  $username . " print report to view");
		
		$selected = "Coil Received report";
		$navigator = array(
			"หน้าแรก" => "/main",
			"Report" => "/report",
			$selected => "",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
				
		$this->template->write_view('content', 'report/coilreceived_report_view', $data);
		$this->template->render();
	}
	
	function slit_report()
	{
		// Read Date
		$sort_column = $this->input->post("sort_column");
		$sort_by = $this->input->post("sort_by");
		
		if (empty($sort_column)) {
			$sort_column = "sortSlitDate";
		}
		if (empty($sort_by)) {
			$sort_by = "desc";
		}
	
		$report_result = $this->report_model->get_vr_slit_report($sort_column, $sort_by);
		$data["report_result"] = $report_result;
		$data["sort_column"] = $sort_column;
		$data["sort_by"] = ("asc" == $sort_by) ? "desc" : "asc";
	
		$username = $this->session->userdata("USERNAME");
		user_log_message("INFO",  $username . " print report to view");
		
		$selected = "Slit report";
		$navigator = array(
			"หน้าแรก" => "/main",
			"Report" => "/report",
			$selected => "",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
				
		$this->template->write_view('content', 'report/slit_report_view', $data);
		$this->template->render();
	}
	
	function save_slitted_coil_report()
	{
		$report_result = $this->report_model->get_vr_slit_report();
		
		$this->db->trans_begin();
		
		$i = 0;
		foreach($report_result as $item)
		{
			
			$coil_group_code = $item["lot"];
			
			$slittedCoil = (int)$this->input->post("slittedCoil_" . $i);
			$slittedWeight = (int)$this->input->post("slittedWeight_" . $i);
			$pastSlittedCoil = (int)$this->input->post("pastSlittedCoil_" . $i);
			$currentDate = $this->input->post("currentDate");
			
			//echo "aaa-->".$maxDate."bb-->".$item["currentDate"];
			
			if($currentDate!= $item["maxDate"] && $slittedCoil>0){
				$this->report_model->insert_actual_slitted($currentDate, $coil_group_code, $slittedCoil, $slittedWeight);								
			}else{
				$this->report_model->update_actual_slitted($currentDate,$coil_group_code, $slittedCoil, $slittedWeight);
			}
			$i++;
		}
		
		if ($this->db->trans_status() === FALSE)
		{
			$this->db->trans_rollback();
		}
		else
		{
			$this->db->trans_commit();
		}
		
		redirect("/report/slit_report");
	}
	
	function delete_all_file()
	{
		delete_files('upload/');
		redirect("/report/production_upload");
	}	
	
	function save_production_report() 
	{
		$report_result = $this->report_model->get_vr_report();
		
		$this->db->trans_begin();
		foreach($report_result as $item)
		{
			$product_display_id = $item["product_display_id"];
			$product_dtl_id = $item["product_dtl_id"];
			
			$stock = $this->input->post("stock_" . $product_dtl_id);
			$total_kg_need = $this->input->post("total_kg_need_" . $product_dtl_id);
			$total_kg_need = -1*$total_kg_need;
			
			$this->report_model->update_stock_kg_need($product_display_id, $stock, $total_kg_need);
			
		}
		
		if ($this->db->trans_status() === FALSE)
		{
			$this->db->trans_rollback();
		}
		else
		{
			$this->db->trans_commit();
		}
		
		redirect("/report/overall_stock");
		
	}
	
	function overall_stock()
	{
	
		$report = $this->report_model->get_vr_overall_stock_report();
		$data["report"] = $report;
	//print_r($report);
		
		$weight = array();
		$coil_weight = array();
		foreach($report as $item)
		{
			$thickness = $item["thickness_rep"];
			
			$sum_weight = $this->order_model->get_sum_weight_by_thickness((double)$thickness);
			
			if ($sum_weight === FALSE || empty($sum_weight))
			{
				$sum_weight = 0;
			}
			$weight[$thickness] = $sum_weight;
			
			$sum_coil_weight = $this->coil_model->get_sum_coil_weight_by_thickness((double)$thickness);
			if ($sum_coil_weight === FALSE || empty($sum_coil_weight))
			{
				$sum_coil_weight = 0;
			}
			$coil_weight[$thickness] = $sum_coil_weight;
			
		}
		$data["weight"] = $weight;
		$data["coil_weight"] = $coil_weight;
	
	
		$selected = "Overall report";
		$navigator = array(
			"หน้าแรก" => "/main",
			"Report" => "/report",
			$selected => "",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
				
		$this->template->write_view('content', 'report/overall_stock_report', $data);
		$this->template->render();
	}
	
	function diary_produce()
	{
		$report = $this->report_model->get_diary_produce();
		$data["report_result"] = $report;
		
		$product = $this->product_model->get_all();
		$product_result = array();
		foreach($product as $item)
		{
			$product_result[$item->product_dtl_id] = $item->product_name_th;
		}
		$data["product_result"] = $product_result;
		
	
	
		$selected = "รายงานการผลิตประจำวัน";
		$navigator = array(
			"หน้าแรก" => "/main",
			"Report" => "/report",
			$selected => "",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
				
		$this->template->write_view('content', 'report/diary_produce', $data);
		$this->template->render();
	}
	
	function performance_report()
	{
		$startDate = $this->input->post("startDate");
		$endDate = $this->input->post("endDate");
		
		if ($startDate === FALSE) {
			$startDate = date('Y-m-d', strtotime("-1 day"));
		}
		
		if ($endDate === FALSE) {
			$endDate = date('Y-m-d', strtotime("-1 day"));
		}
		
		$dateDiff = $this->DateDiff($startDate, $endDate);
		
		$data = array();
		$data["startDate"] = $startDate;
		$data["endDate"] = $endDate;
		// $data["dateDiff"] = $dateDiff;
		
		$selected = "Performance report";
		$navigator = array(
			"หน้าแรก" => "/main",
			"Report" => "/report",
			$selected => "",
		);
		
		$data["navigation"] = build_navigation($selected, $navigator);
		
		// Query
		$this->load->model("machine_model");
		
		$machines = $this->machine_model->get_sum_performance($startDate, $endDate);
		$data["machine"] = $machines;
		
		$config_result = array();
		$film_unit = array();
		
		$special_machine = array();
		$slitter_machine = array();
		
		$this->load->model("machine_manage_model");
		$this->load->model("film_history_model", "fh");
		for($i = 0; $i < count($machines); $i++) 
		{
		
			if ($machines[$i]["machine_type"] == "Y")
			{
				$special_machine[] = $machines[$i];
			}
			else if ($machines[$i]["machine_type"] == "S")
			{
				$slitter_machine[] = $machines[$i];
			}
		
			$mc_id = $machines[$i]["mc_id"];
			
			$result = $this->machine_manage_model->get_sum_performance($mc_id, $startDate, $endDate);
			
			for($j = 0; $j < count($result); $j++) {
				$config_result[$mc_id][$result[$j]["machine_config_id"]] = $result[$j]["duration"];
			}
			
			$film_unit[$mc_id] = $this->fh->get_unit($mc_id, $startDate, $endDate);
		}
		$data["config_result"] = $config_result;
		$data["film_unit"] = $film_unit;
		$data["special_machine"] = $special_machine;
		$data["slitter_machine"] = $slitter_machine;
		
		$slited_coil = $this->report_model->get_all_coil_by_date($startDate, $endDate);
		// print_r($slited_coil);
		$data["slited_coil"] = $slited_coil;
		
		$total_unit_temp = $this->program_model->get_sum_quantity_all_machine($startDate, $endDate);
		$total_unit = array();
		for($i = 0; $i < count($total_unit_temp); $i++) 
		{
			$total_unit[$total_unit_temp[$i]["mc_id"]] = $total_unit_temp[$i]["total_unit"];
		}
		$data["total_unit"] = $total_unit;
		
		$this->load->model("machine_config_model");
		$configs = $this->machine_config_model->get_all();
		$data["config"] = $configs;
		
		$dateDiffPerMachine = $this->report_model->get_date_diff_group_by_machine($startDate, $endDate);
		$dateDiff = array();
		for($i = 0; $i < count($dateDiffPerMachine); $i++)
		{
			$dateDiff[$dateDiffPerMachine[$i]["mc_id"]] = $dateDiffPerMachine[$i]["Total"];
		}
		$data["dateDiff"] = $dateDiff;

		$this->template->write_view('content', 'report/machine_performance_view', $data);
		$this->template->render();
	}
	
	function DateDiff($strDate1,$strDate2)
	{
		return (strtotime($strDate2) - strtotime($strDate1))/  ( 60 * 60 * 24 );  // 1 day = 60*60*24
	}
}